/* Database Handler includes all the necessary functionality for the Android application
 * to interact with the local SQLite database, especially CRUD operations for objects of
 * Building or Inventory type.
 * 
 * Link for SQLite tutorial:
 * www.androidhive.info/2011/11/android-sqlite-database-tutorial/
 */
package com.example.wmuqrreader;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper{
	
	//TODO: Dump log to downloads folder when an error occurs
	
	// Global variables to be used throughout the DatabaseHandler class
	private static final int DATABASE_VERSION 	= 1;
	private static final String DATABASE_NAME 	= "fireInventory";
	private static final String TABLE_INVENTORY = "extinguisher";
	private static final String TABLE_BUILDING 	= "location";
	private static final String TABLE_SERVICE	= "service";
	
	//Inventory column names
	private static final String KEY_ID 			= "id";					//DB identifier, auto increment
	private static final String KEY_QR			= "qr";					//QR code for the device
	private static final String KEY_INVENTORY	= "inventory_number";	//The legacy inventory number of device
	private static final String KEY_LOCATION	= "location";			//Textual description of location
	private static final String KEY_SERIAL		= "serial_number";		//The SN on the device
	private static final String KEY_TYPE		= "type";				//The type of device
	private static final String KEY_BRAND		= "brand";				//The brand of the device
	private static final String KEY_SERVICE		= "service_date";		//Latest service date of device
	private static final String KEY_YEAR		= "manufactured_year";	//Year device was manufactured
	private static final String KEY_INTERVAL	= "service_interval";	//Time between services (in years)
	
	//Service column names
	private static final String KEY_OK			= "found_ok";			//Was the device found in usable condition?
	private static final String KEY_CLEANED		= "cleaned";			//Was the device cleaned?
	private static final String KEY_WIRE		= "replaced_wire";		//Was the wire replaced?
	private static final String KEY_RECHARGE	= "recharged";			//Was the device recharged?
	private static final String KEY_MOUNT		= "new_mount";			//Was the mount replaced?
	private static final String KEY_OTHER		= "other";				//General notes
	private static final String KEY_OFFICER		="officer_name";		//Name of the officer who performed the inspection
	
	//Building column names
	private static final String KEY_NUM			= "building_number";
	private static final String KEY_BUILDING	= "building_name";
	
	public static final String STOCK_BLDG		= "'NEW STOCK'";	//The name of the building where new extinguishers are stored
	
	
	// Constructor
	public DatabaseHandler(Context context){
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	//Only runs when the application is first installed.
	@Override
	public void onCreate(SQLiteDatabase db) {
		
		//Build a queries to create needed tables		
		//NOTE: Changing the order of columns requires a change in the READ functions
		String CREATE_BUILDING_TABLE = "CREATE TABLE " + TABLE_BUILDING + "("
				+ KEY_ID		+ " INTEGER PRIMARY KEY,"
				+ KEY_NUM		+ " TEXT,"
				+ KEY_BUILDING	+ " TEXT UNIQUE" + ")";
		
		String CREATE_SERVICE_TABLE = "CREATE TABLE " + TABLE_SERVICE + "("
				+ KEY_ID 		+ " INTEGER PRIMARY KEY,"
				+ KEY_QR		+ " INTEGER,"		//BOOL would be better here. However, SQLite does not support that data type.
				+ KEY_SERVICE	+ " TEXT,"
				+ KEY_OK		+ " INTEGER,"		//	Instead, we will strictly adhere to using 0 or 1 for True/False columns
				+ KEY_CLEANED	+ " INTEGER,"
				+ KEY_WIRE		+ " INTEGER,"
				+ KEY_RECHARGE	+ " INTEGER,"
				+ KEY_MOUNT		+ " INTEGER,"
				+ KEY_OTHER		+ " TEXT,"
				+ KEY_OFFICER	+ " TEXT"
				+ ")";
		
		String CREATE_INVENTORY_TABLE = "CREATE TABLE " + TABLE_INVENTORY + "("
				+ KEY_ID 		+ " INTEGER PRIMARY KEY,"				
				+ KEY_LOCATION	+ " TEXT,"
				+ KEY_SERIAL	+ " INTEGER,"
				+ KEY_TYPE		+ " TEXT,"
				+ KEY_BRAND		+ " TEXT,"
				+ KEY_SERVICE	+ " INETEGR,"
				+ KEY_YEAR		+ " TEXT,"
				+ KEY_INVENTORY	+ " INTEGER,"
				+ KEY_QR		+ " INTEGER UNIQUE,"
				+ KEY_NUM		+ " INETEGR,"
				+ KEY_INTERVAL	+ " INTEGER, FOREIGN KEY(" + KEY_NUM + ") REFERENCES " + TABLE_BUILDING + "(" + KEY_NUM + ") "				
				+ ")";						
		
		//Execute SQLite queries
		db.execSQL(CREATE_BUILDING_TABLE);
		db.execSQL(CREATE_INVENTORY_TABLE);	
		db.execSQL(CREATE_SERVICE_TABLE);
				
		//Create building 0000 "New Stock" to ensure it's always there
		createDefaults(db);
	}

	//Only runs if changes are made to the database schema
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_BUILDING);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_INVENTORY);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_SERVICE);
 
        // Create tables again
        onCreate(db);
	}
	
	//Creates a list of default values in various tables
	public void createDefaults(SQLiteDatabase db)
	{
		String CREATE_STOCK_BLDG = "INSERT INTO " + TABLE_BUILDING 
				+ "(" + KEY_NUM + "," + KEY_BUILDING + ") "
				+ "VALUES (0, " + STOCK_BLDG + ")";
		
		db.execSQL(CREATE_STOCK_BLDG);
	}
	
	//TODO: All DB operations should be threaded at some point
	//	http://android-developers.blogspot.com/2009/05/painless-threading.html
	
	/*--------------------------------------------------------------------------*/
	/*				Inventory Operations - Create, Read, Update, Delete 		*/
	/*--------------------------------------------------------------------------*/
	
	//CREATE: Adds a new fire extinguisher to the database
	public long addInventory(Inventory inventory){
		try{
			Log.d("SAVE: ", "ID: " + inventory.get_id() + " LOC: " + inventory.get_location() + "BLDG: " + inventory.get_building());
			SQLiteDatabase db = this.getWritableDatabase();
			 
		    ContentValues values = new ContentValues();
		    values.put(KEY_LOCATION, inventory.get_location());	// Inventory location description
		    values.put(KEY_SERIAL, inventory.get_serialNum());	// Inventory Serial
		    values.put(KEY_TYPE, inventory.get_type());
		    values.put(KEY_BRAND, inventory.get_brand());
		    values.put(KEY_SERVICE, inventory.get_svDate());
		    values.put(KEY_YEAR, inventory.get_year());
		    values.put(KEY_INVENTORY, inventory.get_inv());
		    values.put(KEY_QR, inventory.get_qr());
		    values.put(KEY_NUM, inventory.get_building());
		    values.put(KEY_INTERVAL, inventory.get_interval());
		 
		    // Inserting Row
		    long result = db.insert(TABLE_INVENTORY, null, values);
		    Log.d("SAVE: ", "" + result);
		    db.close();
		    return result;
		}
		catch(Exception e)
		{
			Log.d("EXCEPTION:", e.getMessage());
			return -1;
		}
	}
	
	//READ: Getting a single inventory entry
	public Inventory getInventory(int id){
		
		    SQLiteDatabase db = this.getReadableDatabase();
		 
		    Cursor cursor = db.query(TABLE_INVENTORY, new String[] { KEY_ID,
		            KEY_LOCATION, KEY_SERIAL, KEY_TYPE, KEY_BRAND, KEY_SERVICE, KEY_YEAR, KEY_INVENTORY, KEY_QR, KEY_NUM, KEY_INTERVAL }, KEY_ID + "=?",
		            new String[] { String.valueOf(id) }, null, null, null, null);
		    
		    try {
		    	if (cursor != null)
		    		cursor.moveToFirst();		
		    	
			    Inventory inventory = new Inventory(
			    		cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),
			    		 cursor.getString(5), cursor.getString(6), cursor.getInt(7), cursor.getInt(8), cursor.getInt(9), cursor.getInt(10));
			    
			    // return entry
			    return inventory;
		    } 
		    catch(Exception e) {
		    	return new Inventory(-1);
		    }
	}	
	
	//READ: Get a single inventory by QR
	public Inventory getInventory(int qr, String data){
		
	    SQLiteDatabase db = this.getReadableDatabase();
	 
	    Cursor cursor = db.query(TABLE_INVENTORY, new String[] { KEY_ID,
	            KEY_LOCATION, KEY_SERIAL, KEY_TYPE, KEY_BRAND, KEY_SERVICE, KEY_YEAR, KEY_INVENTORY, KEY_QR, KEY_NUM, KEY_INTERVAL }, KEY_QR + "=?",
	            new String[] { String.valueOf(qr) }, null, null, null, null);
	    
	    try {
	    	if (cursor != null)
	    		cursor.moveToFirst();
	 
		    Inventory inventory = new Inventory(
		    		cursor.getInt(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),
		    		 cursor.getString(5), cursor.getString(6), cursor.getInt(7), cursor.getInt(8), cursor.getInt(9), cursor.getInt(10));
		    
		    // return entry
		    return inventory;
	    } 
	    catch(Exception e) {
	    	return new Inventory(-1);
	    }	    
	}
	
	//READ: Getting all entries
	public List<Inventory> getAllInventory(int bldg_id){
		Cursor cursor = null;
		
		Log.d("READ: ", "ENTRIES FROM ");
		
		List<Inventory> inventoryList = new ArrayList<Inventory>();
	 
	    SQLiteDatabase db = this.getWritableDatabase();
	    
	    if(bldg_id == Integer.MIN_VALUE)
	    {
	    	// Select All Query - order by id ASC. Pass WILDCARD % to fetch all, regardless of bldg
	        cursor = db.query(true, TABLE_INVENTORY, null, null, null, null, null, KEY_ID + " ASC", null);
	    }
	    else
	    {
		    // Select All Query - order by id ASC
	        cursor = db.query(true, TABLE_INVENTORY, null, KEY_NUM + "=" + bldg_id, null, null, null, KEY_ID + " ASC", null);
	    }
	 
	    // looping through all rows and adding to list
	    if (cursor.moveToFirst()) {
	        do {
	            Inventory inv = new Inventory();
	            inv.set_id(cursor.getInt(0));
	            inv.set_location(cursor.getString(1));	
	            inv.set_serialNum(cursor.getString(2));	
	            inv.set_type(cursor.getString(3));
	            inv.set_brand(cursor.getString(4));	
	            inv.set_svDate(cursor.getString(5));	
	            inv.set_year(cursor.getString(6));		
	            inv.set_inv(cursor.getInt(7));	
	            inv.set_qr(cursor.getInt(8));	
	            inv.set_building(cursor.getInt(9));	
	            
	            Log.d("READ: ", "ID: "+ inv.get_id() +", LOC: " + inv.get_location() );
	            
	            // Adding contact to list
	            inventoryList.add(inv);
	        } while (cursor.moveToNext());
	    }	  
	    
	    Log.d("READ:", "COMPLETE");
	    
	    // return inventory list
	    return inventoryList;
	}
	
	//UPDATE: Update inventory entry
	public int updateInventory(Inventory inventory){
		try
		{
		SQLiteDatabase db = this.getWritableDatabase();
		 
	    ContentValues values = new ContentValues();
	    values.put(KEY_LOCATION, inventory.get_location());	// Inventory location
	    values.put(KEY_SERIAL, inventory.get_serialNum());	// Inventory Serial
	    values.put(KEY_TYPE, inventory.get_type());
	    values.put(KEY_BRAND, inventory.get_brand());
	    values.put(KEY_SERVICE, inventory.get_svDate());
	    values.put(KEY_YEAR, inventory.get_year());
	    values.put(KEY_INVENTORY, inventory.get_inv());
	    values.put(KEY_QR, inventory.get_qr());
	    values.put(KEY_NUM, inventory.get_building());
	    
	    // updating row
	    return db.update(TABLE_INVENTORY, values, KEY_ID + " = ?",
	            new String[] { String.valueOf(inventory.get_id()) });
		}
		catch(Exception e)
		{
			Log.d("EXCEPTION:", e.getMessage());
			return -1;
		}
	}
	
	//DELETE: remove an entry from the db
	public void deleteInventory(Inventory inventory){
		Log.d("DEL: ", "ID: " + inventory.get_id() + " LOC: " + inventory.get_location());
		SQLiteDatabase db = this.getWritableDatabase();
	    int result = db.delete(TABLE_INVENTORY, KEY_ID + " = ?", new String[] { String.valueOf(inventory.get_id()) });
	    
	    if(result == 1)
	    	Log.d("DEL:", "SUCCESS");
	    else
	    	Log.d("DEL:", "FAILED");
	    
	    db.close();
	}
	
	/*--------------------------------------------------------------------------*/
	/*				Building Operations - Create, Read, Update, Delete 			*/
	/*--------------------------------------------------------------------------*/
	
	//CREATE: Add NEW building
	public long addBuilding(Building building){
		
		try{
			SQLiteDatabase db = this.getWritableDatabase();		//Connect to the android device db
			 
		    ContentValues values = new ContentValues();
		    values.put(KEY_NUM, building.get_num());	// Inventory location
		    values.put(KEY_BUILDING, building.get_building());	// Inventory Serial
		 
		    // Inserting Row
		    long result = db.insert(TABLE_BUILDING, null, values);
		    db.close();
		    
		    return result;
		}
		catch (Exception e){
			Log.d("EXCEPTION:", e.getMessage());
			return -1;
		}
	}
	
	//READ: Get all buildings from the db
	public List<Building> getAllBuilding() {
		Log.d("READ: ", "ENTRIES FROM ");
		
		List<Building> buildingList = new ArrayList<Building>();
	 
		// Connect to android device db
	    SQLiteDatabase db = this.getWritableDatabase();
	    
	    // Select All Query - order by building number ASC
        Cursor cursor = db.query(true, TABLE_BUILDING, null, null, null, null, null, KEY_NUM + " ASC", null);

	 
	    // looping through all rows and adding to list
	    if (cursor.moveToFirst()) {
	        do {
	            Building bldg = new Building();
	            bldg.set_id(cursor.getInt(0));
	            bldg.set_num(cursor.getString(1));
	            bldg.set_building(cursor.getString(2));
	            
	            Log.d("READ: ", "ID: "+ bldg.get_id() +", LOC: " + bldg.get_building() );
	            
	            // Adding contact to list
	            buildingList.add(bldg);
	        } while (cursor.moveToNext());
	    }	  
	    
	    Log.d("READ:", "COMPLETE");
	    
	    
	    // return building list
	    return buildingList;
	}	
	
	//READ: Get all building names from the db (for spinner)
	public List<String> getAllBuildingNames() {
		Log.d("READ: ", "ENTRIES FROM ");
		
		List<String> buildingList = new ArrayList<String>();
	 
		// Connect to android device db
	    SQLiteDatabase db = this.getWritableDatabase();
	    
	    // Select All Query - order by building number ASC
        Cursor cursor = db.query(true, TABLE_BUILDING, null, null, null, null, null, KEY_NUM + " ASC", null);

	 
	    // looping through all rows and adding to list
	    if (cursor.moveToFirst()) {
	        do {
	            Building bldg = new Building();
	            bldg.set_id(cursor.getInt(0));
	            bldg.set_num(cursor.getString(1));
	            bldg.set_building(cursor.getString(2));
	            
	            Log.d("READ: ", "ID: "+ bldg.get_id() +", LOC: " + bldg.get_building() );
	            
	            
	            //String number = String.format("%4s:", Integer.toString(bldg._num) ).replace(' ', '0');
	            
	            // Adding contact to list
	            buildingList.add(bldg._building);
	        } while (cursor.moveToNext());
	    }	  
	    
	    Log.d("READ:", "COMPLETE");
	    
	    
	    // return building list
	    return buildingList;
	}	
	
	//READ: Getting a single building entry
	public Building getBuilding(int id){
		
		    SQLiteDatabase db = this.getReadableDatabase();
		 
		    Cursor cursor = db.query(TABLE_BUILDING, new String[] { KEY_ID, KEY_NUM, KEY_BUILDING }, KEY_ID + "=?",
		            new String[] { String.valueOf(id) }, null, null, null, null);
		    
		    try {
		    	if (cursor != null)
		    		cursor.moveToFirst();
		 
			    Building building = new Building(cursor.getInt(0), cursor.getString(1), cursor.getString(2));
			    
			    // return entry
			    return building;
		    } 
		    catch(Exception e) {
		    	return new Building();
		    }
	}	
	
	//READ: Getting a building ID by name
	public int getBuilding(String bldName){
		
		    SQLiteDatabase db = this.getReadableDatabase();
		 
		    Cursor cursor = db.query(TABLE_BUILDING, new String[] { KEY_ID, KEY_NUM, KEY_BUILDING }, KEY_BUILDING + "=?",
		            new String[] { String.valueOf(bldName) }, null, null, null, null);
		    
		    try {
		    	if (cursor != null)
		    		cursor.moveToFirst();
		 
		    	//Return id of building
			    return cursor.getInt(0);			   
		    } 
		    catch(Exception e) {
		    	return -1;
		    }
	}	
	
	//UPDATE: Update a single building
	public int updateBuilding(Building building){
		try
		{
			SQLiteDatabase db = this.getWritableDatabase();
			 
		    ContentValues values = new ContentValues();
		    values.put(KEY_NUM, building.get_num());
		    values.put(KEY_BUILDING, building.get_building());
		    
		    // updating row
		    return db.update(TABLE_BUILDING, values, KEY_ID + " = ?",
		            new String[] { String.valueOf(building.get_id()) });
		}
		catch(Exception e)
		{
			Log.d("EXCEPTION:", e.getMessage());
			return -1;
		}
	}
	
	//DELETE: 
	public void deleteBuilding(Building building){
		Log.d("DEL: ", "ID: " + building.get_id() + " LOC: " + building.get_building());
		SQLiteDatabase db = this.getWritableDatabase();
	    int result = db.delete(TABLE_BUILDING, KEY_ID + " = ?", new String[] { String.valueOf(building.get_id()) });
	    
	    if(result == 1)
	    {
	    	Log.d("DEL:", building.get_building() + ": SUCCESS");
	    	Log.d("DEL:", "Moving inventory...");
	    	
	    	int stock_id = 1;
	    	
	    	List<Inventory> iList = this.getAllInventory(building._id);
	    	for(Inventory i : iList)
	    	{
	    		i.set_building(stock_id);
	    		this.updateInventory(i);
	    	}
	    	Log.d("DEL:", "COMPLETE");
	    }
	    else
	    	Log.d("DEL:", building.get_building() + ": FAILED");
	    
	    db.close();
	}
		
	/*--------------------------------------------------------------------------*/
	/*				Inspection Operations - Create, Read, Update				*/
	/*--------------------------------------------------------------------------*/
	
	//CREATE: Adds a new fire extinguisher inspection to the database
	public long addInspection(Inspection inspection){
		try{
			
			//Log.d("SAVE: ", "ID: " + inspection.get_id() + " LOC: " + inventory.get_location() + "BLDG: " + inventory.get_building());
			SQLiteDatabase db = this.getWritableDatabase();
			 
		    ContentValues values = new ContentValues();
		    values.put(KEY_QR, inspection.get_qr());	//Matched Inventory QR
		    values.put(KEY_SERVICE, inspection.get_svDate());	// Inventory Serial
		    values.put(KEY_OK, inspection.is_foundOk());
		    values.put(KEY_CLEANED, inspection.is_cleaned());
		    values.put(KEY_WIRE, inspection.is_replacedTie());
		    values.put(KEY_RECHARGE, inspection.is_recharged());
		    values.put(KEY_MOUNT, inspection.is_newMount());
		    values.put(KEY_OTHER, inspection.get_otherNotes());
		    values.put(KEY_OFFICER, inspection.get_officerName());
		 
		    // Inserting Row
		    long result = db.insert(TABLE_SERVICE, null, values);
		    Log.d("SAVE: ", "" + result);
		    db.close();
		    return result;
		}
		catch (Exception e){
			return -1;
		}
	}
	
	//READ: Get a single inventory by QR
	public Inspection getInspection(int inv_id, int index){
		
	    try {
	    	
	    	Inspection gotInspection = new Inspection();
	    	
	    	List<Inspection> inspectionList = getAllInspection(inv_id);
	    	
	    	gotInspection = inspectionList.get(index);
	    	
	    	return gotInspection;
	    } 
	    catch(Exception e) {
	    	return new Inspection();
	    }	    
	}
	
	//READ: Getting all entries
	public List<Inspection> getAllInspection(int inv_id){
		Cursor cursor = null;
		Log.d("READ: ", "ENTRIES FROM ");
		
		List<Inspection> inspectionList = new ArrayList<Inspection>();
	 
	    SQLiteDatabase db = this.getWritableDatabase();
	    
	    if(inv_id == Integer.MIN_VALUE)
	    {
	    	// Select All (regardless of id )Query - order by id ASC. We need this for dumping the db
	    	cursor = db.query(true, TABLE_SERVICE, null, null, null, null, null, KEY_ID + " DESC", null);
	    }
	    else
	    {
	    	// Select All Query - order by id ASC
	    	cursor = db.query(true, TABLE_SERVICE, null, KEY_QR + "=" + inv_id, null, null, null, KEY_ID + " DESC", null);
	    }
	 
	    // looping through all rows and adding to list
	    if (cursor.moveToFirst()) {
	        do {
	            Inspection ins = new Inspection();
	            
	            ins.set_id(cursor.getInt(0));
	            ins.set_qr(cursor.getInt(1));	
	            ins.set_svDate(cursor.getString(2));	
	            ins.set_foundOk( cursor.getInt(3) > 0 ? true : false );
	            ins.set_cleaned( cursor.getInt(4) > 0 ? true : false );		
	            ins.set_replacedTie( cursor.getInt(5) > 0 ? true : false );		
	            ins.set_recharged(cursor.getInt(6) > 0 ? true : false );	
	            ins.set_newMount(cursor.getInt(7) > 0 ? true : false );	
	            ins.set_otherNotes(cursor.getString(8));
	            ins.set_officerName(cursor.getString(9));
	            
	            
	            Log.d("READ: ", "ID: "+ ins.get_id() +", LOC: " + ins.get_svDate() );
	            
	            // Adding contact to list
	            inspectionList.add(ins);
	        } while (cursor.moveToNext());
	    }	  
	    
	    Log.d("READ:", "COMPLETE");
	    
	    // return inventory list
	    return inspectionList;
	}
	
	public List<String> getAllInspectionDates(int inv_id){
		
		Log.d("READ: ", "ENTRIES FROM ");
		
		List<String> inspectionList = new ArrayList<String>();
	 
	    SQLiteDatabase db = this.getWritableDatabase();
	    
	    // Select All Query - order by id ASC
        Cursor cursor = db.query(true, TABLE_SERVICE, null, KEY_QR + "=" + inv_id, null, null, null, KEY_ID + " DESC", null);

	 
	    // looping through all rows and adding to list
	    if (cursor.moveToFirst()) {
	        do {
	            // Adding contact to list
	            inspectionList.add(cursor.getString(2));
	        } while (cursor.moveToNext());
	    }	  
	    
	    Log.d("READ:", "COMPLETE");
	    
	    // return inventory list
	    return inspectionList;
	}


}
